--exec sp_shipment_month_year_report 1
ALTER              proc sp_shipment_month_year_report

@order_type int

as
begin
	-- CoolUnitCost and SOEndUser fields added into results table by Gandhi on 13th Sep 2004
	declare @results Table( ProductID int, COOlQty int, COOLReqDate datetime, COOLSchDate datetime, 
				PartNo varchar(50), CSLITQtyShipped int, CSLITShippedDate datetime, 
				SoNo varchar(50), CustId int, SoPoNo varchar(30), 
				SoBkDate datetime, SoShipVia varchar(100), CustName varchar(65), CustNo varchar(20), 
				CSTstatus varchar(15), CSTShipDate datetime, CSTTrackingNumber varchar(150), COOLUnitCost float, SoEndUser varchar(65),COOlLineItemNo int,IsDistributor int)

	declare @productID int, @cOOlQty int
	declare @cOOLUnitCost float
	declare @cOOLReqDate datetime, @cOOLSchDate datetime
	declare @partNo varchar(50)
	declare @cSLITQtyShipped int
	declare @cSLITShippedDate datetime
	declare @soNo varchar(50)
	declare @cust_id int
	declare @soPoNo varchar(30)
	declare @soEndUser varchar(65)
	declare @soBkDate datetime
	declare @cSTShipVia varchar(100)
	declare @cSTShipMethod varchar(50)
	declare @cSTShipAccount varchar(50)

	declare @custName varchar(65),@custNo varchar(20), @cSTstatus varchar(15)
	
	declare @cSTShipDate datetime
	declare @cSTTrackingNumber varchar(150)
	declare @coolLineItemNo int 
	declare @isdistributor int
	
	declare mainCursor cursor for
	SELECT
		Customer_Open_Order_LineItem.Product_ID, 
		Customer_Open_Order_LineItem.COOL_Qty, 
		Customer_Open_Order_LineItem.COOL_Requested_Date, 
		Customer_Open_Order_LineItem.COOL_Schedule_Date,
		Product.Part_Number,
		Customer_Shipping_Line_Item_Table.CSLIT_Qty_Shipped, 
		Customer_Shipping_Line_Item_Table.CSLIT_Shipped_Date,
		Sales_Order.SalesOrder_No, 
		Sales_Order.Customer_ID, 
		Sales_Order.SO_Customer_PO_Number, 
		Sales_Order.SO_Booking_Date, 
		--Sales_Order.SO_Shipping_Method, 
		--Sales_Order.SO_Shipping_Account,
		Customer_Shipping_Table.CST_Method,
		Customer_Shipping_Table.CST_Account_Number,
		C1.C_Name, C1.C_Number,C1.C_Is_Distributor,
		Customer_Shipping_Table.CST_Status, 
		Customer_Shipping_Table.CST_Ship_Date, 
		Customer_Shipping_Table.CST_AW_Bill_Number,
		Customer_Open_Order_LineItem.COOL_Unit_Cost, 
		--Sales_Order.So_Enduser,
		CASE 
		WHEN ( Sales_Order.SO_ODM_ID IS NULL OR Sales_Order.SO_ODM_ID < 0 ) THEN ''
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO_ODM_ID )
		END as EndUser,
		Customer_Open_Order_LineItem.COOL_Line_Item_No             -- Added by Veeru on 20th Sep 2004
		
		
	FROM
	     ((((Customer_Open_Order_LineItem INNER JOIN  Customer_Shipping_Line_Item_Table ON
	        Customer_Open_Order_LineItem.LineItem_ID = Customer_Shipping_Line_Item_Table.LineItem_ID)
	     INNER JOIN Sales_Order ON Customer_Open_Order_LineItem.SalesOrder_ID = Sales_Order.SalesOrder_ID and Sales_Order.SO_Is_Sample = @order_type)
	     INNER JOIN Product ON Customer_Open_Order_LineItem.Product_ID = Product.Product_ID)
	     INNER JOIN Customer_Shipping_Table ON  Customer_Shipping_Line_Item_Table.CustomerShipping_ID = Customer_Shipping_Table.CustomerShipping_ID)
	     INNER JOIN Customer C1 ON Sales_Order.Customer_ID = C1.Customer_ID	     
	WHERE
	    Customer_Shipping_Table.CST_Status = 'SHIPPED'
	
		open mainCursor
		fetch next from mainCursor into 	
		@productID, @cOOlQty, @cOOLReqDate, @cOOLSchDate, @partNo, @cSLITQtyShipped, @cSLITShippedDate, 
		@soNo, @cust_id, @soPoNo, @soBkDate, @cSTShipMethod, @cSTShipAccount, @custName, @custNo, @isdistributor, @cSTstatus, @cSTShipDate, @CSTTrackingNumber, @cOOLUnitCost, @soEndUser, @coolLineItemNo
		while @@fetch_status = 0
		begin
			--Shipping method is fetching from CST. In CST method and acc are combinedly known as CST_Method hence Iam commenting this code.
			/*if(ltrim(rtrim(@soShipMethod)) = '')
			begin	
				set @soShipVia = @soShipAccount
			end
			else
			begin
				set @soShipVia = @soShipMethod + '   #   ' + @soShipAccount
			end*/
			set @cSTShipVia = @cSTShipMethod

			--blind insert
			insert into @results values(@productID, @cOOlQty, @cOOLReqDate, @cOOLSchDate, @partNo, @cSLITQtyShipped, @cSLITShippedDate, 
			@soNo, @cust_id, @soPoNo, @soBkDate, @cSTShipVia, @custName, @custNo, @cSTstatus, @cSTShipDate, @CSTTrackingNumber, @cOOLUnitCost, @soEndUser,@coolLineItemNo,isnull( @isdistributor, 2 ) )
		
			set @productID=null
			set @cOOlQty=null
			set @cOOLReqDate=null
			set @cOOLSchDate=null
			set @partNo=null
			set @cSLITQtyShipped=null
			set @cSLITShippedDate=null 
			set @soNo=null
			set @cust_id=null
			set @soPoNo=null
			set @soBkDate=null
			set @cSTShipVia=null
			set @cSTShipMethod=null
			set @cSTShipAccount=null 
			set @custName=null
			set @custNo=null
			set @cSTstatus=null
			set @cSTShipDate=null
			set @cSTTrackingNumber=null
			set @coolLineItemNo=null
			set @isdistributor = null

			fetch next from mainCursor into 	
			@productID, @cOOlQty, @cOOLReqDate, @cOOLSchDate, @partNo, @cSLITQtyShipped, @cSLITShippedDate, 
			@soNo, @cust_id, @soPoNo, @soBkDate, @cSTShipMethod, @cSTShipAccount, @custName, @custNo,@isdistributor, @cSTstatus, @cSTShipDate, @CSTTrackingNumber, @cOOLUnitCost, @soEndUser, @coolLineItemNo 
		end
		close mainCursor
		deallocate mainCursor
		-- To Show Distributor Ship Info. @isdistributor = 2 Customer Ship,=1 Distributoor Ship
		INSERT INTO @RESULTS
				(SoNo,SoPoNo,CustId,CustName,SoBkDate,COOlLineItemNo,PartNo,ProductID,COOlQty,COOLReqDate,
				 CSLITQtyShipped,CSTShipDate,COOLUnitCost,IsDistributor,CSLITShippedDate)
		SELECT 
			 DSO.DIST_SALESORDER_NO,DSO.CUSTOMER_PO,DSO.CUSTOMER_ID,
			(SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = DSO.CUSTOMER_ID ) AS CUSTOMER_NAME,
			--(SELECT C_SALES_PERSON FROM CUSTOMER WHERE CUSTOMER_ID = DSO.CUSTOMER_ID ) AS SALES_PERSON,
			DSO.SALESORDER_DATE,DLIT.DIST_LINEITEM_NO, 
			(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = DLIT.PRODUCT_ID) AS PRODUCT,DLIT.PRODUCT_ID,
			DLIT.DIST_REQ_QTY,DLIT.DIST_REQ_DATE,DLIT.DIST_SHIP_QTY,DLIT.DIST_SHIP_DATE,DLIT.UNIT_COST,2,DSO.ACTUAL_SHIP_DATE
		FROM 
			 (DIST_SALES_ORDER DSO INNER JOIN DIST_LINE_ITEM_TABLE DLIT ON
		          DSO.DIST_SALESORDER_ID = DLIT.DIST_SALESORDER_ID)

	select * from @results

end